{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from tasks.backend import engine_md, with_db_session_p\n",
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "保利1号\ntrade_date  2017-10-23  2017-10-24  2017-10-25\nname                                          \nST中南             14.60       14.58       14.51\n中泰化学             14.41       14.29       14.49\n乐视网              15.33       15.33       15.33\n华映科技              5.55        5.53        5.55\n哈投股份              8.27        8.26        8.22\n如意集团             16.75       16.66       16.28\n方大集团              7.53        7.43        7.60\n智光电气              6.65        6.61        6.64\n森源电气             15.41       15.24       15.29\n永利股份             15.12       15.04       15.26\n江淮汽车             10.59       10.49       10.54\n清水源              17.98       17.98       17.98\n湘电股份             13.09       12.96       12.99\n瑞康医药             14.92       14.61       14.81\n皖江物流              4.48        4.31        4.33\n益丰药房             38.28       39.03       39.17\n省广集团              6.46        6.42        6.31\n索菲亚              40.50       41.20       42.50\n航天科技             27.80       27.79       28.09\n证通电子             12.83       12.79       12.99\n金雷股份             20.54       20.41       20.59\n长江证券              9.35        9.49        9.47\n领益智造              8.69        8.60        8.70\n鸿博股份             12.96       12.67       12.69\n龙宇燃油             14.74       14.46       14.03\n"
     ]
    }
   ],
   "source": [
    "sql_str = \"\"\"\n",
    "select md.ts_code,name, trade_date, close\n",
    "from (\n",
    "       select md.ts_code, trade_date, close\n",
    "       from tushare_stock_daily_md md\n",
    "       where trade_date between '2017-10-23' and '2017-10-25'\n",
    "       and md.ts_code in (select ts_code from tushare_stock_info where symbol in (\"002600\", \"600575\", \"603939\", \"002572\", \"000783\", \"000055\", \"600418\", \"002092\", \"002193\", \"300230\", \"002358\", \"300443\", \"002589\", \"002229\", \"002445\", \"002197\", \"600416\", \"300437\", \"002400\", \"603003\", \"600864\", \"000536\", \"002169\", \"000901\", \"300104\"))\n",
    "     ) md\n",
    "left join (\n",
    "       select ts_code, name from tushare_stock_info\n",
    "       where symbol in (\"002600\", \"600575\", \"603939\", \"002572\", \"000783\", \"000055\", \"600418\", \"002092\", \"002193\", \"300230\", \"002358\", \"300443\", \"002589\", \"002229\", \"002445\", \"002197\", \"600416\", \"300437\", \"002400\", \"603003\", \"600864\", \"000536\", \"002169\", \"000901\", \"300104\")\n",
    "     ) info\n",
    "on md.ts_code=info.ts_code\n",
    "\"\"\"\n",
    "df = pd.read_sql(sql_str, engine_md)\n",
    "\n",
    "md_df = df.pivot(index='name', columns='trade_date', values='close')\n",
    "print('保利1号')\n",
    "print(md_df)\n",
    "md_df.to_csv('保利1号.csv', encoding='GBK')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "保利15号\ntrade_date  2017-12-14  2017-12-15\nname                              \nST中南             12.95       12.95\n中泰化学             13.77       13.57\n乐视网              15.33       15.33\n华映科技              4.75        4.66\n哈投股份              7.51        7.58\n如意集团             16.13       16.30\n方大集团              6.22        6.15\n智光电气              5.26        5.24\n森源电气             14.86       14.86\n永利股份             12.73       12.53\n江淮汽车              9.67        9.54\n清水源              15.81       16.18\n湘电股份             12.77       13.06\n瑞康医药             13.69       13.44\n皖江物流              4.10        4.09\n益丰药房             44.34       43.62\n省广集团              5.74        5.65\n索菲亚              34.25       34.24\n航天科技             24.90       24.99\n证通电子             12.69       12.69\n金雷股份             16.90       16.96\n长江证券              8.40        8.35\n鸿博股份              9.90        9.88\n龙宇燃油             12.07       11.93\n"
     ]
    }
   ],
   "source": [
    "sql_str = \"\"\"\n",
    "select md.ts_code,name, trade_date, close\n",
    "from (\n",
    "       select md.ts_code, trade_date, close\n",
    "       from tushare_stock_daily_md md\n",
    "       where trade_date between '2017-12-14' and '2017-12-16'\n",
    "       and md.ts_code in (select ts_code from tushare_stock_info where symbol in (\n",
    "       \"600575\",\"603939\",\"002572\",\"000783\",\"000055\",\"002092\",\"600418\",\"002193\",\"300230\",\"002358\",\"300443\",\"002589\",\"002445\",\"002229\",\"002197\",\"600416\",\"002400\",\"300437\",\"603003\",\n",
    "       \"600864\",\"000536\",\"002169\",\"000901\",\"300104\")\n",
    "       )\n",
    "     ) md\n",
    "left join (\n",
    "       select ts_code, name from tushare_stock_info\n",
    "       where symbol in (\n",
    "       \"600575\",\"603939\",\"002572\",\"000783\",\"000055\",\"002092\",\"600418\",\"002193\",\"300230\",\"002358\",\"300443\",\"002589\",\"002445\",\"002229\",\"002197\",\"600416\",\"002400\",\"300437\",\"603003\",\n",
    "       \"600864\",\"000536\",\"002169\",\"000901\",\"300104\"\n",
    "       )\n",
    "     ) info\n",
    "on md.ts_code=info.ts_code\n",
    "\"\"\"\n",
    "df = pd.read_sql(sql_str, engine_md)\n",
    "\n",
    "md_df = df.pivot(index='name', columns='trade_date', values='close')\n",
    "print('保利15号')\n",
    "print(md_df)\n",
    "md_df.to_csv('保利15号.csv', encoding='GBK')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
